<?php
require_once '../db_connect.php';
//Refine_word　絞込ワード
	$refine_sql = '
		select distinct volunteer_information_id,volunteer_name,content
		from (volunteer_information Voinf left join sub_category sCate on Voinf.sub_category_id = sCate.sub_category_id)
			left join category mCate
				on mCate.main_category_id = sCate.main_category_id
		where
	';
	$refine_count_sql = '
		select distinct count(volunteer_information_id)
		from (volunteer_information Voinf left join sub_category sCate on Voinf.sub_category_id = sCate.sub_category_id)
			left join category mCate
				on mCate.main_category_id = sCate.main_category_id
		where
	';

	$flag = false;
	$refine_word = array();
	if (!empty($_POST['refine_word_sub_category']))
	{
		array_push($refine_word,htmlspecialchars($_POST['refine_word_sub_category']));
		if ($flag)
		{
			$refine_sql = $refine_sql." and sCate.sub_category_name like ?";
			$refine_count_sql = $refine_count_sql." and sCate.sub_category_name like ?";
		}
		else
		{
			$refine_sql = $refine_sql." sCate.sub_category_name like ?";
			$refine_count_sql = $refine_count_sql." sCate.sub_category_name like ?";
			$flag = true;
		}
	}

	if (!empty($_POST['refine_word_pref']))
	{
		array_push($refine_word,htmlspecialchars($_POST['refine_word_pref']));
		if ($flag)
		{
			$refine_sql = $refine_sql." and Voinf.pref like ?";
			$refine_count_sql = $refine_count_sql." and Voinf.pref like ?";
		}
		else
		{
			$refine_sql = $refine_sql." Voinf.pref like ?";
			$refine_count_sql = $refine_count_sql." Voinf.pref like ?";
			$flag = true;
		}
	}

	if (!empty($_POST['refine_word_gender']))
	{
		array_push($refine_word,htmlspecialchars($_POST['refine_word_gender']));
		if ($flag)
		{
			$refine_sql = $refine_sql." and Voinf.target_gender like ?";
			$refine_count_sql = $refine_count_sql." and Voinf.target_gender like ?";
		}
		else
		{
			$refine_sql = $refine_sql." Voinf.target_gender like ?";
			$refine_count_sql = $refine_count_sql." Voinf.target_gender like ?";
			$flag = true;
		}
	}

	if (!empty($_POST['refine_free_word']))
	{
		for ($i = 0;$i <= 7;$i++)
		{
			array_push($refine_word,"%".htmlspecialchars($_POST['refine_free_word'])."%");
		}
		if ($flag)
		{
			$refine_sql = $refine_sql." and (Voinf.volunteer_name like ?
										or Voinf.content like ?
										or Voinf.target_gender like ?
										or Voinf.location like ?
										or Voinf.access like ?
										or Voinf.pref like ?
										or sCate.sub_category_name like ?
										or mCate.main_category_name like ?)";

			$refine_count_sql = $refine_count_sql." and (Voinf.volunteer_name like ?
										or Voinf.content like ?
										or Voinf.target_gender like ?
										or Voinf.location like ?
										or Voinf.access like ?
										or Voinf.pref like ?
										or sCate.sub_category_name like ?
										or mCate.main_category_name like ?)";
		}
		else
		{
			$refine_sql = $refine_sql." (Voinf.volunteer_name like ?
										or Voinf.content like ?
										or Voinf.target_gender like ?
										or Voinf.location like ?
										or Voinf.access like ?
										or Voinf.pref like ?
										or sCate.sub_category_name like ?
										or mCate.main_category_name like ?)";

			$refine_count_sql = $refine_count_sql." (Voinf.volunteer_name like ?
										or Voinf.content like ?
										or Voinf.target_gender like ?
										or Voinf.location like ?
										or Voinf.access like ?
										or Voinf.pref like ?
										or sCate.sub_category_name like ?
										or mCate.main_category_name like ?)";
			$flag = true;
		}
	}
	if ($flag)
	{
		$max_limit = 10;//最大10件表示
		$start_limit = 0;//表示を開始する行数

		$refine_sql .= " and current_date() <= Voinf.end_time";
		$refine_count_sql .= " and current_date() <= Voinf.end_time";
		$refine_sql .= " order by updata_date desc limit ?,?";
		$count_refine_word = $refine_word;
		array_push($refine_word,$start_limit);
		array_push($refine_word,$max_limit);

		$sth = $db->prepare($refine_count_sql);
		$sth = $db->execute($sth,$count_refine_word);
		echo "<center><a>検索の結果".array_shift($sth->fetchRow(DB_FETCHMODE_ORDERED))."件見つかりました</a></center>";

		$sth = $db->prepare($refine_sql);
		$sth = $db->execute($sth,$refine_word);
		while ($row =& $sth->fetchRow(DB_FETCHMODE_ASSOC))
		{
			echo "<table id='table-02'><tr><th width='120px'>ボランティア名</th><a href='../volunteer_function/display_volunteer_information.php?volunteer_information_id=".$row['volunteer_information_id']."'><td valign='middle'>".$row['volunteer_name']."</td></a></tr><tr><th>内容</th><td>".$row['content']."</td></tr></tabel>";
		}
//<a href='../volunteer_function/display_volunteer_information.php?volunteer_information_id=".$row['volunteer_information_id']."'>
		$sth = $db->prepare($refine_count_sql);
		$sth = $db->execute($sth,$count_refine_word);
		$sth = ceil(array_shift($sth->fetchRow(DB_FETCHMODE_ORDERED))/10);

		echo "<center><table><tr>";
			for ($i = 1; $i <= $sth; $i++)
			{
				echo "<td><a href='search_display_refine.php?page=".$i."&page_refine=".$refine_word."&target=".$targetpage."'/>".$i."</td>";
			}
		echo "</tr></table></center>";
	}